Imports System
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.Office.Interop
Imports MigraDoc.DocumentObjectModel
Imports MigraDoc.Rendering

Public Class <Database>DataClass

    Public Shared Function GetConnection() As SqlConnection
        Dim connectionString As String _
            = "<ConnectionString>"
        Return New SqlConnection(connectionString)
    End Function

    Public Function getIdent_Current(ByVal Table As String) As Integer
        Dim query As String
        Dim connection As New SqlConnection
        Dim command As New SqlCommand
        Dim reader As SqlDataReader
        Dim returnValue As Integer = 0

        query = "SELECT IDENT_CURRENT('" & Table & "')"
        connection = GetConnection()
        command = New SqlCommand(query, connection)
        command.CommandType = CommandType.Text
        Try
            connection.Open()
            reader = command.ExecuteReader()
            If reader.HasRows = True Then
                While reader.Read()
                    returnValue = System.Convert.ToInt32(reader.GetValue(0))
                End While
            End If
            reader.Close()
            connection.Close()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        Finally
            connection.Close()
        End Try
        Return returnValue
    End Function

    Public Function getIdent_Incr(ByVal Table As String) As Integer
        Dim query As String
        Dim connection As New SqlConnection
        Dim command As New SqlCommand
        Dim reader As SqlDataReader
        Dim returnValue As Integer = 0

        query = "SELECT IDENT_INCR('" & Table & "')"
        connection = GetConnection()
        command = New SqlCommand(query, connection)
        command.CommandType = CommandType.Text
        Try
            connection.Open()
            reader = command.ExecuteReader()
            If reader.HasRows = True Then
                While reader.Read()
                    returnValue = System.Convert.ToInt32(reader.GetValue(0))
                End While
            End If
            reader.Close()
            connection.Close()
        Catch ex As SqlException
            MessageBox.Show(ex.Message, ex.GetType.ToString)
        Finally
            connection.Close()
        End Try
        Return returnValue
    End Function

    Public Function getAutoID(ByVal Mode As String, ByVal Table As String) As Integer
        Dim Ident_Current As Integer = getIdent_Current(Table)
        If Mode = "Last" Then
            Return Ident_Current
        ElseIf Mode = "New" Then
            Return Ident_Current + getIdent_Incr(Table)
        End If
        Return Nothing
    End Function

    Public Sub ExportToExcel(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGrid, ByVal GridRow As DataRowView, ByVal RowNum As Integer)
        Try
            Dim dt As DataTable
            If Type = "One" Then
                dt = DataGridRow_To_DataTable(Grid, GridRow, Head, RowNum)
            Else
                dt = DataGrid_To_DataTable(Grid, Head)
            End If

            If (dt.Columns.Count > 0) Then
                Dim oExcel As Excel.Application
                Dim oBook As Excel.Workbook
                Dim oSheet As Excel.Worksheet
                oExcel = CType(CreateObject("Excel.Application"), Excel.Application)
                oExcel.Visible = False
                oBook = oExcel.Workbooks.Add
                oSheet = CType(oBook.Worksheets(1), Excel.Worksheet)

                oSheet.Range("B2").Value = Head
                oSheet.Range("B2").Font.Bold = True

                If Type = "One" Then

                    For s As Integer = 0 To dt.Rows.Count - 1
                        oSheet.Range("B" & 4 + s).Value = dt.Rows.Item(s).Item(0).ToString
                        oSheet.Range("C" & 4 + s).Value = dt.Rows.Item(s).Item(1).ToString
                    Next

                    oSheet.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    Dim DataArrayHead(0, 0 To Grid.Columns.Count - 1) As Object
                    For s As Integer = 0 To Grid.Columns.Count - 1
                        DataArrayHead(0, s) = Grid.Columns(s).Header.ToString
                    Next

                    oSheet.Range("B4").Resize(1, Grid.Columns.Count).Value = DataArrayHead
                    oSheet.Range("B4").Resize(1, Grid.Columns.Count).Font.Bold = True

                    Dim DataArray(0 To Grid.Items.Count - 1, 0 To Grid.Columns.Count - 1) As Object
                    For r As Integer = 0 To dt.Rows.Count - 1
                        For s As Integer = 0 To dt.Columns.Count - 1
                            DataArray(r, s) = dt.Rows(r).Item(s).ToString
                        Next
                    Next

                    oSheet.Range("B5").Resize(Grid.Items.Count, Grid.Columns.Count).Value = DataArray
                    oSheet.Columns.AutoFit()

                End If

                oExcel.Visible = True
                oExcel = Nothing
                oSheet = Nothing
            End If
        Catch
        End Try
    End Sub

    Public Sub ExportToWord(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGrid, ByVal GridRow As DataRowView, ByVal RowNum As Integer)
        Try
            Dim dt As DataTable
            If Type = "One" Then
                dt = DataGridRow_To_DataTable(Grid, GridRow, Head, RowNum)
            Else
                dt = DataGrid_To_DataTable(Grid, Head)
            End If

            If (dt.Columns.Count > 0) Then
                Dim oWord As Word.Application
                Dim oDoc As Word.Document
                Dim oTable As Word.Table
                Dim oPara1 As Word.Paragraph

                oWord = CType(CreateObject("Word.Application"), Word.Application)
                oWord.Visible = False
                oDoc = oWord.Documents.Add
                With oDoc.PageSetup
                    .PaperSize = Word.WdPaperSize.wdPaperA4
                    .Orientation = Word.WdOrientation.wdOrientLandscape
                    .LeftMargin = oWord.CentimetersToPoints(1)
                    .BottomMargin = oWord.CentimetersToPoints(1)
                    .TopMargin = oWord.CentimetersToPoints(1)
                End With

                oPara1 = oDoc.Content.Paragraphs.Add
                oPara1.Range.Text = Head
                oPara1.Range.Font.Bold = CInt(False)
                oPara1.Range.Font.Size = 8
                oPara1.Format.SpaceAfter = 8
                oPara1.Range.InsertParagraphAfter()

                If Type = "One" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, dt.Rows.Count, 2)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To dt.Rows.Count - 1
                        oTable.Cell(i + 1, 1).Range.InsertAfter(dt.Rows.Item(i).Item(0).ToString)
                        oTable.Cell(i + 1, 2).Range.Text = dt.Rows.Item(i).Item(1).ToString
                    Next

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                ElseIf Type = "Many" Then

                    oTable = oDoc.Tables.Add(oDoc.Bookmarks.Item("\endofdoc").Range, dt.Rows.Count + 1, dt.Columns.Count)
                    oTable.Range.ParagraphFormat.SpaceAfter = 2

                    For i As Integer = 0 To dt.Columns.Count - 1
                        oTable.Cell(1, i + 1).Range.InsertAfter(dt.Columns(i).Caption.ToString)
                    Next

                    For r As Integer = 0 To dt.Rows.Count - 1
                        For s As Integer = 0 To dt.Columns.Count - 1
                            oTable.Cell(r + 2, s + 1).Range.Text = dt.Rows(r).Item(s).ToString
                        Next
                    Next

                    oTable.Rows.Item(1).Cells.Shading.BackgroundPatternColorIndex = Word.WdColorIndex.wdGray25

                    oTable.Borders.InsideLineStyle = Word.WdLineStyle.wdLineStyleSingle
                    oTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleDouble

                    oTable.AllowAutoFit = True
                    oTable.Columns.AutoFit()

                End If

                oWord.Visible = True
                oWord.NormalTemplate.Saved = True
                oWord = Nothing
                oDoc = Nothing
            End If
        Catch
        End Try
    End Sub

    Public Sub ExportToPdf(ByVal Type As String, ByVal Head As String, ByVal Grid As DataGrid, ByVal GridRow As DataRowView, ByVal RowNum As Integer)
        Try
            Dim dt As DataTable
            If Type = "One" Then
                dt = DataGridRow_To_DataTable(Grid, GridRow, Head, RowNum)
            Else
                dt = DataGrid_To_DataTable(Grid, Head)
            End If
            Dim pdfForm As New PDFform(dt, Head, Type)

            Dim document As Document = pdfForm.CreateDocument()
            document.UseCmykColor = True
            Dim pdfRenderer As New PdfDocumentRenderer(True)
            pdfRenderer.Document = document
            pdfRenderer.RenderDocument()

            Dim FilePath As String = System.IO.Path.GetTempPath & "\" & Date.Now.Second.ToString & Date.Now.Millisecond.ToString & ".pdf"
            pdfRenderer.Save(FilePath)
            Process.Start(FilePath)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.ReadLine()
        End Try
    End Sub

    'Converts the DataGrid to DataTable
    Public Function DataGrid_To_DataTable(dg As System.Windows.Controls.DataGrid, tblName As [String], Optional minRow As Integer = 0) As DataTable
        Dim dt As New DataTable(tblName)

        For Each column As DataGridColumn In dg.Columns
            Dim dc As New DataColumn(column.Header.ToString())
            dt.Columns.Add(dc)
        Next

        For i As Integer = 0 To dg.Items.Count - 1
            Dim row As DataRowView = CType(dg.Items.GetItemAt(i), DataRowView) ''dg.Items(i)
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dg.Columns.Count - 1
                dr(j) = If((row.Item(j).ToString Is Nothing), "", row.Item(j).ToString())
            Next
            dt.Rows.Add(dr)
        Next

        ' Related to the bug around min size when using ExcelLibrary for export
        For i As Integer = dg.Items.Count To minRow - 1
            Dim dr As DataRow = dt.NewRow()
            For j As Integer = 0 To dt.Columns.Count - 1
                dr(j) = "  "
            Next
            dt.Rows.Add(dr)
        Next
        Return dt
    End Function

    'Converts the DataGridRow to DataTable
    Public Function DataGridRow_To_DataTable(dg As System.Windows.Controls.DataGrid, dgr As DataRowView, tblName As [String], ByVal RowNum As Integer, Optional minRow As Integer = 0) As DataTable
        Try
            Dim dt As New DataTable(tblName)

            ' Header columns
            Dim dc1 As New DataColumn("Column")
            dt.Columns.Add(dc1)
            Dim dc2 As New DataColumn("Value")
            dt.Columns.Add(dc2)

            Dim row As DataRowView = CType(dg.Items.GetItemAt(RowNum), DataRowView)
            For j As Integer = 0 To dg.Columns.Count - 1
                Dim dr As DataRow = dt.NewRow()
                dr.Item(0) = dg.Columns(j).Header.ToString
                dr.Item(1) = If((row.Item(j).ToString Is Nothing), "", row.Item(j).ToString())
                dt.Rows.Add(dr)
            Next
            Return dt
        Catch ex As Exception
            Console.WriteLine(ex.Message)
            Console.ReadLine()
        End Try
        Return Nothing
    End Function

End Class

Public Class WaitCursor
    Implements IDisposable

    Private _previousCursor As Cursor

    Public Sub New()
        _previousCursor = Mouse.OverrideCursor

        Mouse.OverrideCursor = Cursors.Wait
    End Sub

    Public Overloads Sub Dispose() Implements IDisposable.Dispose
        Mouse.OverrideCursor = _previousCursor
    End Sub

End Class


